﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class BillDB : DBAccess
    {
        /// <summary>
        /// Create a bill
        /// </summary>
        /// <param name="_employee"></param>
        /// <param name="TableId"></param>
        /// <returns>Bill</returns>
        public Bill AddNewBill(Employee _employee, int TableId)
        {
            Bill _bill = null;
            string sql = string.Format("INSERT INTO Bill(Datecreate,EmployeeId,TableId,Status) VALUES('{0}',{1},{2},{3})", DateTime.Now.ToString(), _employee.EmployeeId, TableId, 1);
            try
            {
                this.ExecuteNonQuery(sql, CommandType.Text);
                sql = "SELECT * FROM Bill ORDER BY DateCreate DESC";
                SqlDataReader rd = this.ExecuteQuery(sql, CommandType.Text);
                if (rd.Read())
                {
                    _bill = new Bill();
                    Table _tabletemp = new Table();
                    _bill.BillId = int.Parse(rd["billId"].ToString());
                    _bill.Datecreate = Convert.ToDateTime(rd["DateCreate"].ToString());
                    _tabletemp.TableId = int.Parse(rd["tableId"].ToString());
                    _bill.Table = _tabletemp;
                    _bill.Status = (BillStatus)(int.Parse(rd["status"].ToString()));
                    rd.Close();
                }
                return _bill;
            }
            catch
            {
                return _bill;
            }
        }

        public int GetBillidFromTableId(int tableid)
        {  
            string query = "SELECT billid FROM tableid WHERE status=1 OR status=2";
            try
            {
                SqlDataReader rd = ExecuteQuery(query, CommandType.Text);
                if (rd.Read())
                {
                    int billid = int.Parse(rd["BillId"].ToString());

                    rd.Close();
                    return billid;
                }
                return -1;
            }
            catch
            {
                return -1;
            }
        }

        /// <summary>
        /// return a bill
        /// </summary>
        /// <param name="billId"></param>
        /// <returns></returns>
        public Bill getBill(int billId)
        {
            IList<BillDetail> listBillDetail = new List<BillDetail>();
            Bill bill = null;
            try
            {
                SqlDataReader rd = ExecuteQuery("getBill", CommandType.StoredProcedure, "@billId", billId);
                while (rd.Read())
                {
                    bill = new Bill();
                    bill.BillId = billId;

                    BillDetail billDetail = new BillDetail();
                    Product product = new Product();
                    product.ProductId = int.Parse(rd["ProductId"].ToString());
                    product.Name = rd["Name"].ToString();
                    billDetail.Product = product;
                    billDetail.Price = float.Parse(rd["Price"].ToString());
                    billDetail.Quantity = int.Parse(rd["Quantity"].ToString());
                    listBillDetail.Add(billDetail);
                    bill.Listbilldetail = listBillDetail;

                    bill.Status = (BillStatus)(int.Parse(rd["Status"].ToString()));
                    Table table = new Table();
                    table.TableId = int.Parse(rd["TableId"].ToString());
                    bill.Table = table;
                    int tableextend = 0;
                    if (!(int.TryParse(rd["TableExtend"].ToString(), out tableextend)))
                        bill.TableExtent = 0;
                    else
                        bill.TableExtent = tableextend;
                }
                rd.Close();
                return bill;
            }
            catch
            {
                return bill;
            }
        }

        public void UpdateExtendTable(Bill _bill)
        {
            try
            {
                string sql = string.Format("update Bill set tableExtend={0} where BillId={1}", _bill.TableExtent, _bill.BillId);
                ExecuteNonQuery(sql, CommandType.Text);
            }
            catch
            {
            }
        }

        /// <summary>
        /// move a table
        /// </summary>
        /// <param name="tablesource">table need to move</param>
        /// <param name="tabledestination">moved to table</param>
        /// <param name="employeeid"></param>
        public void movetable(int tableIDsource, int tableIDdestination, int employeeid)
        {
            Employee emplpoyee = new Employee();
            emplpoyee.EmployeeId = employeeid;
            Bill fbill = AddNewBill(emplpoyee, tableIDdestination);
            string sql = string.Format("UPDATE Bill SET tableid={0} WHERE tableid={1} AND status=1", tableIDdestination, tableIDsource);
            ExecuteNonQuery(sql, CommandType.Text);
        }
            
        public DataTable TotalPrice(int frommonth, int fromyear, int tomonth, int toyear)
        {
            Connect();
            DataTable tb = new DataTable();
            //SqlDataAdapter da = new SqlDataAdapter("select * from viewTotalPrice",conn);
            //da.Fill(ds);
            tb = ExecuteQueryTable("sp_totalPrice", CommandType.StoredProcedure, "@frommonth", frommonth, "@fromyear", fromyear, "@tomonth", tomonth, "@toyear", toyear);

            return tb;
        }

        public DataTable TotalPrice(DateTime fromday, DateTime today)
        {
            Connect();
            DataTable tb = new DataTable();
            //SqlDataAdapter da = new SqlDataAdapter("select * from viewTotalPrice",conn);
            //da.Fill(ds);
            tb = ExecuteQueryTable("sp_totalPrice", CommandType.StoredProcedure, "@fromday", fromday, "@today", today);

            return tb;
        }

        public DataTable TotalPrice(int today, int tomonth, int toyear)
        {
            Connect();
            DataTable tb = new DataTable();
            //SqlDataAdapter da = new SqlDataAdapter("select * from viewTotalPrice",conn);
            //da.Fill(ds);
            tb = ExecuteQueryTable("sp_totalPricetoday", CommandType.StoredProcedure, "@day", today, "@month", tomonth, "@year", toyear);

            return tb;
        }
    }
}
